SET NOCOUNT ON;
declare @version smallint = 2;
declare @release smallint = 3;
declare @script_name varchar(256)
set @script_name='UtilityDB_V'+convert(varchar(5),@version)+'R'
	+right('00000'+convert(varchar(5),@release),5)+'.sql'

if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=0)
	print 'script '+@script_name+' already executed, please undo then re-execute.';
else
begin try
begin tran
/*--Begin Code Here --*/

declare @tsql varchar(max);
set @tsql='
create view udb.io_file_stats_overview 
as
with cte_iofs as
(
select 
record_date
,database_id
,file_id
,num_of_reads
,num_of_bytes_read 
,io_stall_read_ms 
,num_of_writes 
,num_of_bytes_written 
,io_stall_write_ms
,io_stall
,size_on_disk_bytes 
,row_number() over(partition by database_id,file_id order by record_date) as row_order
from 
udb.io_file_stats 
)
select
cte1.record_date as [start_date]
,cte2.record_date as [end_date]
,db_name(cte1.database_id) as [db_name]
,mf.name as [file_name]
,datediff(ms,cte1.record_date,cte2.record_date) as [ms]
,datediff(ss,cte1.record_date,cte2.record_date) as [seconds]
,cte2.num_of_reads-cte1.num_of_reads as [read_io]
,(cte2.num_of_reads-cte1.num_of_reads)/datediff(ss,cte1.record_date,cte2.record_date) as [read_iops]
,cte2.num_of_writes-cte1.num_of_writes as [write_io]
,(cte2.num_of_writes-cte1.num_of_writes)/datediff(ss,cte1.record_date,cte2.record_date) as [write_iops]
,cte2.num_of_reads-cte1.num_of_reads+cte2.num_of_writes-cte1.num_of_writes as [io]
,(cte2.num_of_reads-cte1.num_of_reads+cte2.num_of_writes-cte1.num_of_writes)/datediff(ss,cte1.record_date,cte2.record_date) as [iops]
,cte2.num_of_bytes_read-cte1.num_of_bytes_read as [read_bytes]
,(cte2.num_of_bytes_read-cte1.num_of_bytes_read)/datediff(ss,cte1.record_date,cte2.record_date)/1024 as [read_kbytes_per_second]
,case (cte2.num_of_reads-cte1.num_of_reads) when 0 then 0 else (cte2.num_of_bytes_read-cte1.num_of_bytes_read)/1024/(cte2.num_of_reads-cte1.num_of_reads) end as [read_avg_size_kbytes]
,cte2.num_of_bytes_written-cte1.num_of_bytes_written as [write_bytes]
,(cte2.num_of_bytes_written-cte1.num_of_bytes_written)/datediff(ss,cte1.record_date,cte2.record_date)/1024 as [write_kbytes_per_second]
,case (cte2.num_of_writes-cte1.num_of_writes) when 0 then 0 else (cte2.num_of_bytes_written-cte1.num_of_bytes_written)/1024/(cte2.num_of_writes-cte1.num_of_writes) end as [write_avg_size_kbytes]
,cte2.io_stall_read_ms-cte1.io_stall_read_ms as [read_ms_wait]
,(cte2.io_stall_read_ms-cte1.io_stall_read_ms)/datediff(ss,cte1.record_date,cte2.record_date) as [avg_read_ms_wait_per_second]
,cte2.io_stall_write_ms-cte1.io_stall_write_ms as [write_ms_wait]
,(cte2.io_stall_write_ms-cte1.io_stall_write_ms)/datediff(ss,cte1.record_date,cte2.record_date) as [avg_write_ms_wait_per_second]
,cte2.size_on_disk_bytes-cte1.size_on_disk_bytes as [change_in_file_size_bytes]
,cte2.size_on_disk_bytes/1024 as [size_on_disk_KB]
,datepart(year,cte1.record_date) as [year]
,datepart(month,cte1.record_date) as [month]
,datepart(dd,cte1.record_date) as [day]
,datepart(hh,cte1.record_date) as [hour]
,datepart(mi,cte1.record_date) as [minute]
,datepart(ss,cte1.record_date) as [second]
from
cte_iofs cte1
inner join
cte_iofs cte2
on
cte1.database_id =cte2.database_id
and
cte1.file_id = cte2.file_id 
and
cte1.row_order+1=cte2.row_order
inner join
sys.master_files mf
on
mf.database_id=cte1.database_id
and
mf.file_id=cte1.file_id
;'

execute (@tsql);

/*--End Code Here --*/
if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=1)
	update dbo.database_version_information 
	set undone=0
	,date_released=getdate()
	where
	script_name=@script_name;
else
	insert into dbo.database_version_information 
	values(@script_name,@version,@release,GETDATE(),0);


print 'script '+@script_name+' succeeded!'
commit tran
end try
begin catch 
	declare @error_message varchar(2000);
	set @error_message='SCRIPT:'+@script_name+' FAILED! ROLLBACK!  ERROR_MESSAGE:'+coalesce(error_message(),'')+' ERROR_NUMBER:'+coalesce(convert(varchar(10),error_number()),'');
	ROLLBACK
	raiserror(@error_message,16,1)
end catch